﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;
using ChinaDynastyTours.Helpers;

namespace ChinaDynastyTours.Controls
{
    class BillCtrl
    {
        #region 获取计划收入款项信息


        
        /// <summary>
        /// 获取对账单数据
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        internal static DataTable GetBillData(string tourgroupId, string loginProviderID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql;

            Sql = string.Format(@"SELECT expenseid FROM fee_Expense WHERE TourgroupID='{0}' AND ProviderID={1}", tourgroupId, loginProviderID);
            dbCommand = db.GetSqlStringCommand(Sql);
            object obj = db.ExecuteScalar(dbCommand);

            if (obj != null)
            {
                Sql = string.Format(@"SELECT *,e.Status,(Quantum*UnitPrice*PeopleNumber) as Total FROM fee_Bill b,fee_expense e WHERE b.ExpenseID=e.ExpenseID AND b.ExpenseID={0} AND b.TourgroupID='{1}' AND b.ProviderID={2} ORDER BY b.ExpenseType", obj, tourgroupId, loginProviderID);
                dbCommand = db.GetSqlStringCommand(Sql);
                DataSet ds = db.ExecuteDataSet(dbCommand);
                if (ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            else
                return null;
        }

        #endregion

        #region 计划收入款项信息的增、删、改

        internal static string CreateBillData(BillEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO [fee_Bill]([ExpenseID], [TourgroupID], [ProviderID], [ExpenseType],[Remark],[Quantum],[UnitPrice],[PeopleNumber])
                                         VALUES({0},'{1}',{2},'{3}','{4}',{5},{6},{7});Select SCOPE_IDENTITY();",
                                        entity.ExpenseID,
                                        entity.TourgroupID,
                                        entity.ProviderID,
                                        entity.ExpenseType,
                                        entity.Remark,
                                        entity.Quantum,
                                        entity.UnitPrice,
                                        entity.PeopleNumber);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            return db.ExecuteScalar(dbCommand).ToString();
        }

        /// <summary>
        /// 更新内容
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        internal static int UpdateBillData(BillEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE [fee_Bill]
                                         SET [ExpenseType]='{0}', [Remark]='{1}',[Quantum]={2},[UnitPrice]={3},[PeopleNumber]={4}
                                         WHERE BillID={5}",
                                        entity.ExpenseType,
                                        entity.Remark,
                                        entity.Quantum,
                                        entity.UnitPrice,
                                        entity.PeopleNumber,
                                        entity.BillID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="billId"></param>
        /// <returns></returns>
        internal static int DeleteBillData(string billId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM [fee_Bill] WHERE BillID={0}", billId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion

        /// <summary>
        /// 查看导游安排情况，确定地接社是否能够提交“账单”
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns>true可以，false不可以</returns>
        //internal static bool EnableSubmitExpenseBillDataForGuideArrange(string tourgroupId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    DbCommand dbCommand;
        //    string Sql = string.Format(@"SELECT * FROM orgt_TourGroup WHERE TourgroupID='{0}' AND HasGuide=1 AND EXISTS(SELECT * FROM cic_GuideArrange WHERE TourgroupID='{0}')", tourgroupId);
        //    dbCommand = db.GetSqlStringCommand(Sql);
        //    object obj = db.ExecuteScalar(dbCommand);

        //    if (obj != null)//大行程成要求安排导游，且在导游安排计划中也已经安排了导游
        //        return true;
        //    else
        //        return false;
        //}
        /// <summary>
        /// 查看餐厅安排情况，确定地接社是否能够提交“账单”
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns>true可以，false不可以</returns>
        internal static bool EnableSubmitExpenseBillDataForDiningArrange(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            bool enableSubmit = false;

            //大行程计划中安排了除‘早’或“”外的餐厅要求（如午晚、午、早午晚等），且本地行程中（Sch_ProvidersPlanSchedule）存在没有安排的则不允许填写账单
            string Sql = string.Format(@"   SELECT count(p.Dining) FROM Sch_ProvidersPlanSchedule p,orgt_TourGroupSchedule t 
                                            WHERE p.TourGroupScheduleID=t.TourGroupScheduleID AND p.TourgroupID='DT110957  ' AND (t.Dining<>'' AND t.Dining<>'早') 
                                            AND (p.Dining is NULL OR p.Dining='')", tourgroupId);
            dbCommand = db.GetSqlStringCommand(Sql);
            object obj = db.ExecuteScalar(dbCommand);
            switch (obj.ToString())
            {
                case "0":   //符合要求（不存在Dining字段为NULL或“”的内容），不存在未安排的餐厅内容
                    enableSubmit = true;
                    break;
                default:    //不符合要求（存在Dining字段为NULL或“”内容），存在未安排的餐厅内容
                    break;
            }
            //如果团队人数>10，则不受餐厅安排的限制
            Sql = string.Format(@"SELECT SUM(AdultNum) FROM orgt_TourGroup WHERE TourgroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(Sql);
            object total = db.ExecuteScalar(dbCommand);
            if (total != null && int.Parse(total.ToString()) > 10)
                enableSubmit = true;

            return enableSubmit;
        }
    }
}
